Location Setup

NOTE! Location setup changed in db patch 6.2.16 refer to older documentation for previous versions.

General

Location setup is controlled by settings in core.setting and three tables raw.LOCATION_EXTRA, core.location_mapping_setup and core.location_transaction_setup.

The settings are:

  • core_include_all_locations_by_default (default: true) if all ERP locations should be included in raw.LOCATION by default or not. If set to false what locations to include must be configured in core.location_mapping_setup.
  • core_location_type_default (default: warehouse) Default location type used for ERP locations if nothing else is mapped. Possible values are warehouse and store. Exceptions from this default setting must be configured in core.location_mapping_setup.
  • inventory_item_map_sale_default (default: true) if sale transactions should be included in sales history for locations by default.
  • inventory_item_map_transfer_as_sale_default (default: false) if transfer transactions should be included in sales history for locations by default.
  • inventory_item_map_stock_default (default: true) if stock data should be included by default.
  • inventory_item_map_undelivered_default (default: true) if undelivered stock (purchase and transfer orders) should be included by default.
  • inventory_item_map_reserved_default (default: true) if reserved stock should be included by default.

The config tables raw.LOCATION_EXTRA, core.location_mapping_setup and core.location_transaction_setup, should be used for exceptions to the default settings.

  • raw.LOCATION_EXTRA is used to add locations that do not exist in the data coming from the ERP.
  • core.location_mapping_setup affects how the locations in raw.LOCATION and their data enters the staging database and is usually used for:
    • Combining the data from two or more ERP locations.
    • Exclude certain ERP locations and their data if core_include_all_locations_by_default setting is set to true.
    • Include only certain ERP locations and their data if core_include_all_locations_by_default setting is set to false.
    • Configure the location type if not provided by the ERP data mapping. Note only exceptions from the core_location_type_default have to be configured.
  • core.location_transaction_setup affects how AGR sees the locations mapped trough core.location_mapping_setup and their data e.g.:
    • You can select what transaction should be included as sales for the sales history in AGR.
    • You can copy data from one location to another and even pick what data you want copied. This can be used to populate sum-locations with data or e.g. copy only the undelivered from one location to another location.

raw.LOCATION_EXTRA

[NO] [NVARCHAR](255) NOT NULL:  location No.
[NAME] [NVARCHAR](255) NOT NULL: location Name
[GROUP] [NVARCHAR](255) NULL: location Group
[TYPE] [NVARCHAR](255) NULL: location type, store or warehouse, if empty `core_location_type_default` setting will be used
[CLOSED] [BIT] NOT NULL: True if the location is closed False if not
[OPENING_DATE] [DATE] NULL: Not used as is
[CLOSING_DATE] [DATE] NULL: Not used as is

ERP locations are mapped into raw.LOCATION and the table also includes custom locations created in raw.LOCATION_EXTRA. These can be locations that only do not exists as locations in the ERP but should be in AGR e.g. e-commerce store. If a location with the same NO as a custom location arrives from the ERP then that location takes over the custom one. This is done so that a location can be created in AGR ahead of time before it is created in the ERP.

core.location_mapping_setup

[location_no] [NVARCHAR](255) NOT NULL:  This references the location_no in raw.LOCATION
[location_no_target] [NVARCHAR](255) NOT NULL: This references the target location_no in raw.LOCATION
[location_type] [NVARCHAR](10) NULL: location type, store or warehouse
[include] [BIT] NOT NULL: Should the entry be included (True) or excluded (False)
[is_virtual] [BIT] NOT NULL: True if the location should only exist for data mapping purposes and should not be shown in AGR.

If the table is left empty and core_include_all_locations_by_default is set to true, then all locations that appear in raw.LOCATION are mapped trough.

If location types are provided in the erp data mapping or you want to use the default setting in core_location_type_default the location_type column can be left as NULL.

In combination with the core_location_type_default setting you can set location types using the location_type column.

Setting the core_location_type_default to store you only have to specify the location type for the warehouse locations in core.location_mapping_setup if no location type data is provided by the erp.

The stg_element prep_location_map must run for changes in core.location_mapping_setup and default settings to take effect. It uses the view prep.v_location_map to populate the table prep.location_map. prep.location_map is then used in other views that include location data e.g. prep.v_location_and_vendor and inv.v_histories_sale.

Example 1: Exclude a location

If you want to exclude one or more locations but still include all others you can add an entry like this with core_include_all_locations_by_default is set to true.

Here we want to exclude the location B910.

core.location_mapping_setup

location_no location_no_target location_type include is_virtual
B910 B910 NULL 0 0

Example 2: Include only one location

To include only certain locations you can add an entry like this with core_include_all_locations_by_default’ is set to false.

Here we want to only include the location B200.

core.location_mapping_setup

location_no location_no_target location_type include is_virtual
B200 B200 store 1 0

Example 3: Map all data from one location to another

If you want to combine two locations (note that the target location must also be a location listed in raw.LOCATION). Note that each source location can only be mapped to one target location.

Here we want to include all data from B910 in the location B900. B910 will therefor not be a location within AGR.

core.location_mapping_setup

location_no location_no_target location_type include is_virtual
B900 B900 warehouse 1 0
B910 B900 warehouse 1 0

Example 4: Include location as a “virtual” location

As core.location_mapping_setup controls what locations are available to use if a location is only needs to be included to be used in core.location_transaction_setup it must be marked as is_virtual. Then the location will not appear in AGR but the transactional data on that location can be mapped to other locations

Here we want to only include the location B104 as a virtual location.

core.location_mapping_setup

location_no location_no_target location_type include is_virtual
B104 B104 NULL 1 1

core.location_transaction_setup

[id] [INT] IDENTITY(1,1) NOT NULL: Identity field and primary key
[location_no] [NVARCHAR](128) NOT NULL:  This references the location_no in inv.locations
[source_location_list] [NVARCHAR](MAX) NULL: What location/locations are the source of the transactions. If using more than one source location you must provide a comma separated list of location nos. If left as NULL [location_no] is used as the source.
[sale] [BIT] NOT NULL: Map sales, affects [inv].[histories_sale]
[transfer_as_sale] [BIT] NOT NULL: Map transfer transactions as sale, affects [inv].[histories_sale]
[stock] [BIT] NOT NULL: Map stock, affects [inv].[histories_stock], [inv].[stocks]
[undelivered] [BIT] NOT NULL: Map undelivered, affects [inv].[undelivered]
[reserved] [BIT] NOT NULL: Map reserved, affects [inv].[reserved]

It is possible to change how transactional data is mapped to each and every location. By default all locations are linked “to themselves”, with the default settings in core.setting for sale,transfer_as_sale,stock,undelivered and reserved. Keep in mind that if a row for a location is added this default linking is no longer active (see Example 2).

The stg_element inv_item_map_populate must run for changes in core.location_transaction_setup and default settings to take effect. The output is a list of connections between item_ids created by the procedure inv.item_map_populate in the table inv.item_map. If a more specific transactional data mapping between locations is needed, it is possible to create a custom version of the procedure inv.item_map_populate that populates the table inv.item_map as needed.

Example 1: Change settings for one location

If you want to map transactional data for a location differently than the default settings a row can be added with that location as location_no and settings set as wanted.

Here we do not want to include sales or transfers for the location B910 in AGR.

core.location_transaction_setup

location_no source_location_list sale transfer_as_sale stock undelivered reserved
B910 B910 0 0 1 1 1

Example 2: Include undelivered from a “virtual” location

If you need to include e.g. undelivered from a location that should otherwise not appear in AGR (B104), then mark the location as is_virtual in core.location_mapping_setup and then it can be used in core.location_transaction_setup.

core.location_transaction_setup

location_no location_no_target location_type include is_virtual
B104 B104 store 1 1

As we started configuring B910 we must add a row linking B910 to it self otherwise B910 will only contain undelivered from B104 and nothing else.

core.location_transaction_setup

location_no source_location_list sale transfer_as_sale stock undelivered reserved
B910 B104 0 0 0 1 0
B910 B910 1 1 1 1 1

Example 3: Include sale from a list of location

Here we want the items in location B910 to include the sales from B103, B104, B105, and B110, but stocks, undelivered and reserved from B910.

core.location_transaction_setup

location_no source_location_list sale transfer_as_sale stock undelivered reserved
B910 B910 0 0 1 1 1
B910 B103,B104,B105,B110 1 0 0 0 0